﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data.SQLite;
using System.Data;
using Microsoft.Win32;

namespace RainWorkTools
{
    /// <summary>
    /// MainWindow.xaml 的交互逻辑
    /// </summary>
    public partial class MainWindow : Window
    {
        SQLiteConnection conn;
        string connString = "Data Source =./data.db;version=3;Charset=UTF-8";
        public MainWindow()
        {
            InitializeComponent();
        }

        private void btn1_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                conn = new SQLiteConnection(connString);
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                    string sql;
                    SQLiteCommand cmd;
                    //sql = "insert into elf(busno,busname,public,private,password) values('0004','万兴街店','bwk0004A','bwk0004B','1235')";
                    //cmd = new SQLiteCommand(sql, conn);
                    //cmd.ExecuteNonQuery();

                    sql = "select busno as 门店编码,busname as 门店名称,public as 对公,private as 对私,password as 密码 from elf";
                    cmd = new SQLiteCommand(sql, conn);
                    SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    adapter.Fill(dt);

                    dataGrid.ItemsSource = dt.DefaultView;
                }
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btn2_Click(object sender, RoutedEventArgs e)
        {
            //提示导入模板
            MessageBox.Show("第一列：门店编码\n"+
                            "第二列：门店名称\n"+
                            "第三列：对公\n"+
                            "第四列：对私\n"+
                            "第五列：密码","导入格式",MessageBoxButton.OK,MessageBoxImage.Information);            
                
            string filePath = null;
            OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.Filter = "Excel|*.xlsx|Excel 2003|*.xls";
            if (openFileDialog.ShowDialog() == true)
            {
                filePath = openFileDialog.FileName;
            

                //读取文件
                DataTable dt= new DataTable();
                dt = ExcelOperation.ReadFromExcel<DataTable>(filePath);

                //显示读取结果
                dataGrid.ItemsSource= dt.DefaultView;                

                //写入本地数据库
                string sql = "insert into elf(busno,busname,public,private,password) values(@value1,@value2,@value3,@value4,@value5)";
                SQLiteTransaction transaction = null;
                try
                {
                    conn = new SQLiteConnection(connString);
                    if (conn.State != ConnectionState.Open)
                        conn.Open();

                    //启动事务
                    transaction = conn.BeginTransaction();

                    //执行插入操作
                    SQLiteCommand cmd = new SQLiteCommand(sql, conn);
                    foreach (DataRow dr in dt.Rows)
                    {
                        cmd.Parameters.AddWithValue("@value1", dr[0].ToString());
                        cmd.Parameters.AddWithValue("@value2", dr[1].ToString());
                        cmd.Parameters.AddWithValue("@value3", dr[2].ToString());
                        cmd.Parameters.AddWithValue("@value4", dr[3].ToString());
                        cmd.Parameters.AddWithValue("@value5", dr[4].ToString());
                        cmd.ExecuteNonQuery();
                    }

                    //提交事务
                    transaction.Commit();
                    int nums = dt.Rows.Count;
                    MessageBox.Show("导入成功："+nums+" 行");
                }
                catch(Exception ex)
                {
                    transaction.Rollback();
                    MessageBox.Show("导入失败！\n"+ex.Message);
                }
            }

        }
    }
}
